{smcl}
{com}{sf}{ul off}{txt}{.-}
      name:  {res}<unnamed>
       {txt}log:  {res}C:\Users\myogo\Princeton Dropbox\Motohiro Yogo\Koijen Yogo - International\JPE\Replication\Code\1 Data\Treasury.smcl
  {txt}log type:  {res}smcl
 {txt}opened on:  {res}31 Oct 2025, 09:19:09
{txt}
{com}. /* Define local and global variables */
> 
> local directory = "../../Data/US Treasury";
{txt}
{com}. do global;
{txt}
{com}. /* global.do (STATA)
>         Define global variables in "Data".
>         by Ralph Koijen & Motohiro Yogo */
. 
. #delimit ;
{txt}delimiter now ;
{com}. clear all;
{res}{txt}
{com}. global year_min = 2003;
{txt}
{com}. global year_max = 2020;
{txt}
{com}. global small = 5e-4;
{txt}
{com}.         /* Minimum reportable threshold for CPIS */

{txt}end of do-file

{com}. /* Step 1: Construct debt data */
> 
> /* Load LT debt data */
> 
> tempfile Debt;
{txt}
{com}. /* 2003-2006 */
> 
> forval y = $year_min/2006 {c -(};
{txt}  2{com}.         if `y'==$year_min {c -(};
{txt}  3{com}.                 local file = "Table 18";
{txt}  4{com}.         {c )-};
{txt}  5{com}.         else {c -(};
{txt}  6{com}.                 local file = "Table 21";
{txt}  7{com}.         {c )-};
{txt}  8{com}.         /* Load data */
> 
>         import excel using "`directory'/shca`y'/`file'",
>                 first allstring clear;
{txt}  9{com}.         /* Rename variables */
> 
>         rename Countryorregion Name;
{txt} 10{com}.         rename US amountUSA;
{txt} 11{com}.         rename Euro amountEUR;
{txt} 12{com}.         rename JapaneseYen amountJPN;
{txt} 13{com}.         rename UKPound amountGBR;
{txt} 14{com}.         cap rename OwnCurrency amountOWN;
{txt} 15{com}.         cap rename Other amount_OC;
{txt} 16{com}.         /* Drop total and footnotes */
> 
>         gen byte total = sum(Name=="Total");
{txt} 17{com}.         drop if total | missing(Total);
{txt} 18{com}.         drop total;
{txt} 19{com}.         /* Construct variables */
> 
>         gen int year = `y';
{txt} 20{com}.         gen byte type = 2;
{txt} 21{com}.         /* Save data */
> 
>         if `y'!=$year_min       append using `Debt';
{txt} 22{com}.         save `Debt', replace;
{txt} 23{com}. {c )-};
{res}{text}(7 vars, 127 obs)
{res}{txt}(1 observation deleted)
{p 0 4 2}
(file {bf}
C:\Users\myogo\AppData\Local\Temp\ST_9840_000001.tmp{rm}
not found)
{p_end}
{p 0 4 2}
file {bf}
C:\Users\myogo\AppData\Local\Temp\ST_9840_000001.tmp{rm}
saved
as .dta format
{p_end}
{res}{text}(7 vars, 112 obs)
{res}{txt}(1 observation deleted)
{p 0 4 2}
file {bf}
C:\Users\myogo\AppData\Local\Temp\ST_9840_000001.tmp{rm}
saved
as .dta format
{p_end}
{res}{text}(7 vars, 113 obs)
{res}{txt}(1 observation deleted)
{p 0 4 2}
file {bf}
C:\Users\myogo\AppData\Local\Temp\ST_9840_000001.tmp{rm}
saved
as .dta format
{p_end}
{res}{text}(7 vars, 113 obs)
{res}{txt}(1 observation deleted)
{p 0 4 2}
file {bf}
C:\Users\myogo\AppData\Local\Temp\ST_9840_000001.tmp{rm}
saved
as .dta format
{p_end}

{com}. /* 2007-2012 */
> 
> forval y = 2007/2012 {c -(};
{txt}  2{com}.         if `y'==2007 {c -(};
{txt}  3{com}.                 local sheet = "Table 24";
{txt}  4{com}.                 local cell = "A4";
{txt}  5{com}.         {c )-};
{txt}  6{com}.         else if `y'==2008 {c -(};
{txt}  7{com}.                 local sheet = "TB24";
{txt}  8{com}.                 local cell = "A5";
{txt}  9{com}.         {c )-};
{txt} 10{com}.         else {c -(};
{txt} 11{com}.                 local sheet = "A6";
{txt} 12{com}.                 local cell = "A5";
{txt} 13{com}.         {c )-};
{txt} 14{com}.         /* Load data */
> 
>         import excel using "`directory'/shca`y'/shc`y'r-appx",
>                 sheet("`sheet'") cellra(`cell') first allstring clear;
{txt} 15{com}.         /* Rename variables */
> 
>         rename Countryorregion Name;
{txt} 16{com}.         rename US amountUSA;
{txt} 17{com}.         rename Euro amountEUR;
{txt} 18{com}.         rename JapaneseYen amountJPN;
{txt} 19{com}.         rename UKPound amountGBR;
{txt} 20{com}.         rename OwnCurrency amountOWN;
{txt} 21{com}.         keep Name-amountOWN;
{txt} 22{com}.         /* Drop total and footnotes */
> 
>         gen byte total = sum(Name=="Total");
{txt} 23{com}.         drop if total | missing(Total);
{txt} 24{com}.         drop total;
{txt} 25{com}.         /* Construct variables */
> 
>         gen int year = `y';
{txt} 26{com}.         gen byte type = 2;
{txt} 27{com}.         /* Save data */
> 
>         append using `Debt';
{txt} 28{com}.         save `Debt', replace;
{txt} 29{com}. {c )-};
{res}{text}(9 vars, 134 obs)
{res}{txt}(20 observations deleted)
{p 0 4 2}
file {bf}
C:\Users\myogo\AppData\Local\Temp\ST_9840_000001.tmp{rm}
saved
as .dta format
{p_end}
{res}{text}(7 vars, 141 obs)
{res}{txt}(26 observations deleted)
{p 0 7 2}
(variable
{bf:amountUSA} was {bf:str6}, now {bf:str7} to accommodate using data's values)
{p_end}
{p 0 4 2}
file {bf}
C:\Users\myogo\AppData\Local\Temp\ST_9840_000001.tmp{rm}
saved
as .dta format
{p_end}
{res}{text}(34 vars, 261 obs)
{res}{txt}(143 observations deleted)
{p 0 4 2}
file {bf}
C:\Users\myogo\AppData\Local\Temp\ST_9840_000001.tmp{rm}
saved
as .dta format
{p_end}
{res}{text}(34 vars, 261 obs)
{res}{txt}(145 observations deleted)
{p 0 4 2}
file {bf}
C:\Users\myogo\AppData\Local\Temp\ST_9840_000001.tmp{rm}
saved
as .dta format
{p_end}
{res}{text}(7 vars, 266 obs)
{res}{txt}(145 observations deleted)
{p 0 4 2}
file {bf}
C:\Users\myogo\AppData\Local\Temp\ST_9840_000001.tmp{rm}
saved
as .dta format
{p_end}
{res}{text}(11 vars, 266 obs)
{res}{txt}(141 observations deleted)
{p 0 4 2}
file {bf}
C:\Users\myogo\AppData\Local\Temp\ST_9840_000001.tmp{rm}
saved
as .dta format
{p_end}

{com}. /* 2013-2020 */
> 
> forval y = 2013/$year_max {c -(};
{txt}  2{com}.         if `y'==2013 {c -(};
{txt}  3{com}.                 local file = "appendix_tab06";
{txt}  4{com}.         {c )-};
{txt}  5{com}.         else if `y'==2014 {c -(};
{txt}  6{com}.                 local file = "shc14_app06";
{txt}  7{com}.         {c )-};
{txt}  8{com}.         else if `y'==2015 {c -(};
{txt}  9{com}.                 local file = "shc15_app06";
{txt} 10{com}.         {c )-};
{txt} 11{com}.         else {c -(};
{txt} 12{com}.                 local file = "shc_app06_`y'";
{txt} 13{com}.         {c )-};
{txt} 14{com}.         /* Load data */ 
> 
>         import delimited using "`directory'/shca`y'/`file'",
>                 varn(5) case(preserve) rowr(6) stringc(_all) clear;
{txt} 15{com}.         /* Rename variables */
> 
>         rename Countryorregionofissuer Name;
{txt} 16{com}.         cap rename US amountUSA;
{txt} 17{com}.         cap rename USdollar amountUSA;
{txt} 18{com}.         rename Euro amountEUR;
{txt} 19{com}.         cap rename Yen amountJPN;
{txt} 20{com}.         cap rename yen amountJPN;
{txt} 21{com}.         cap rename Pound amountGBR;
{txt} 22{com}.         cap rename pound amountGBR;
{txt} 23{com}.         cap rename Currency amountOWN;
{txt} 24{com}.         cap rename currency amountOWN;
{txt} 25{com}.         keep Name-amountOWN;
{txt} 26{com}.         /* Drop total and footnotes */
> 
>         gen byte total = sum(Name=="Total");
{txt} 27{com}.         drop if total | missing(Total);
{txt} 28{com}.         drop total;
{txt} 29{com}.         /* Construct variables */
> 
>         gen int year = `y';
{txt} 30{com}.         gen byte type = 2;
{txt} 31{com}.         /* Save data */
> 
>         append using `Debt';
{txt} 32{com}.         save `Debt', replace;
{txt} 33{com}. {c )-};
{res}{txt}(encoding automatically selected: ISO-8859-1)
{res}{text}(7 vars, 162 obs)
{res}{txt}(35 observations deleted)
{p 0 7 2}
(variable
{bf:Name} was {bf:str41}, now {bf:str157} to accommodate using data's values)
{p_end}
{p 0 4 2}
file {bf}
C:\Users\myogo\AppData\Local\Temp\ST_9840_000001.tmp{rm}
saved
as .dta format
{p_end}
{res}{txt}(encoding automatically selected: ISO-8859-1)
{res}{text}(9 vars, 169 obs)
{res}{txt}(38 observations deleted)
{p 0 7 2}
(variable
{bf:Name} was {bf:str41}, now {bf:str157} to accommodate using data's values)
{p_end}
{p 0 4 2}
file {bf}
C:\Users\myogo\AppData\Local\Temp\ST_9840_000001.tmp{rm}
saved
as .dta format
{p_end}
{res}{txt}(encoding automatically selected: ISO-8859-1)
{res}{text}(9 vars, 174 obs)
{res}{txt}(34 observations deleted)
{p 0 7 2}
(variable
{bf:Name} was {bf:str41}, now {bf:str157} to accommodate using data's values)
{p_end}
{p 0 4 2}
file {bf}
C:\Users\myogo\AppData\Local\Temp\ST_9840_000001.tmp{rm}
saved
as .dta format
{p_end}
{res}{txt}(encoding automatically selected: ISO-8859-1)
{res}{text}(9 vars, 172 obs)
{res}{txt}(34 observations deleted)
{p 0 7 2}
(variable
{bf:Name} was {bf:str41}, now {bf:str157} to accommodate using data's values)
{p_end}
{p 0 4 2}
file {bf}
C:\Users\myogo\AppData\Local\Temp\ST_9840_000001.tmp{rm}
saved
as .dta format
{p_end}
{res}{txt}(encoding automatically selected: ISO-8859-1)
{res}{text}(9 vars, 178 obs)
{res}{txt}(34 observations deleted)
{p 0 7 2}
(variable
{bf:Name} was {bf:str41}, now {bf:str157} to accommodate using data's values)
{p_end}
{p 0 4 2}
file {bf}
C:\Users\myogo\AppData\Local\Temp\ST_9840_000001.tmp{rm}
saved
as .dta format
{p_end}
{res}{txt}(encoding automatically selected: ISO-8859-1)
{res}{text}(9 vars, 176 obs)
{res}{txt}(34 observations deleted)
{p 0 7 2}
(variable
{bf:Name} was {bf:str41}, now {bf:str157} to accommodate using data's values)
{p_end}
{p 0 4 2}
file {bf}
C:\Users\myogo\AppData\Local\Temp\ST_9840_000001.tmp{rm}
saved
as .dta format
{p_end}
{res}{txt}(encoding automatically selected: ISO-8859-1)
{res}{text}(9 vars, 178 obs)
{res}{txt}(34 observations deleted)
{p 0 7 2}
(variable
{bf:Name} was {bf:str41}, now {bf:str157} to accommodate using data's values)
{p_end}
{p 0 4 2}
file {bf}
C:\Users\myogo\AppData\Local\Temp\ST_9840_000001.tmp{rm}
saved
as .dta format
{p_end}
{res}{txt}(encoding automatically selected: ISO-8859-1)
{res}{text}(9 vars, 164 obs)
{res}{txt}(18 observations deleted)
{p 0 7 2}
(variable
{bf:Name} was {bf:str40}, now {bf:str157} to accommodate using data's values)
{p_end}
{p 0 4 2}
file {bf}
C:\Users\myogo\AppData\Local\Temp\ST_9840_000001.tmp{rm}
saved
as .dta format
{p_end}

{com}. /* Load ST debt data */
> 
> /* 2003-2006 */
> 
> forval y = $year_min/2006 {c -(};
{txt}  2{com}.         if `y'==$year_min {c -(};
{txt}  3{com}.                 local file = "Table 19";
{txt}  4{com}.         {c )-};
{txt}  5{com}.         else {c -(};
{txt}  6{com}.                 local file = "Table 22";
{txt}  7{com}.         {c )-};
{txt}  8{com}.         /* Load data */
> 
>         import excel using "`directory'/shca`y'/`file'",
>                 first allstring clear;
{txt}  9{com}.         /* Rename variables */
> 
>         rename Countryorregion Name;
{txt} 10{com}.         rename US amountUSA;
{txt} 11{com}.         rename Euro amountEUR;
{txt} 12{com}.         rename JapaneseYen amountJPN;
{txt} 13{com}.         rename UKPound amountGBR;
{txt} 14{com}.         cap rename OwnCurrency amountOWN;
{txt} 15{com}.         cap rename Other amount_OC;
{txt} 16{com}.         /* Drop total and footnotes */
> 
>         gen byte total = sum(Name=="Total");
{txt} 17{com}.         drop if total | missing(Total);
{txt} 18{com}.         drop total;
{txt} 19{com}.         /* Construct variables */
> 
>         gen int year = `y';
{txt} 20{com}.         gen byte type = 1;
{txt} 21{com}.         /* Save data */
> 
>         append using `Debt';
{txt} 22{com}.         save `Debt', replace;
{txt} 23{com}. {c )-};
{res}{text}(7 vars, 127 obs)
{res}{txt}(1 observation deleted)
{p 0 7 2}
(variable
{bf:Name} was {bf:str27}, now {bf:str157} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:Total} was {bf:str6}, now {bf:str13} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:amountUSA} was {bf:str6}, now {bf:str13} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:amountEUR} was {bf:str5}, now {bf:str13} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:amountJPN} was {bf:str5}, now {bf:str13} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:amountGBR} was {bf:str4}, now {bf:str13} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:amount_OC} was {bf:str4}, now {bf:str5} to accommodate using data's values)
{p_end}
{p 0 4 2}
file {bf}
C:\Users\myogo\AppData\Local\Temp\ST_9840_000001.tmp{rm}
saved
as .dta format
{p_end}
{res}{text}(7 vars, 66 obs)
{res}{txt}(1 observation deleted)
{p 0 7 2}
(variable
{bf:Name} was {bf:str27}, now {bf:str157} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:Total} was {bf:str6}, now {bf:str13} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:amountUSA} was {bf:str6}, now {bf:str13} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:amountEUR} was {bf:str5}, now {bf:str13} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:amountJPN} was {bf:str5}, now {bf:str13} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:amountGBR} was {bf:str4}, now {bf:str13} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:amount_OC} was {bf:str4}, now {bf:str5} to accommodate using data's values)
{p_end}
{p 0 4 2}
file {bf}
C:\Users\myogo\AppData\Local\Temp\ST_9840_000001.tmp{rm}
saved
as .dta format
{p_end}
{res}{text}(7 vars, 59 obs)
{res}{txt}(1 observation deleted)
{p 0 7 2}
(variable
{bf:Name} was {bf:str27}, now {bf:str157} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:Total} was {bf:str6}, now {bf:str13} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:amountUSA} was {bf:str6}, now {bf:str13} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:amountEUR} was {bf:str5}, now {bf:str13} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:amountJPN} was {bf:str4}, now {bf:str13} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:amountGBR} was {bf:str4}, now {bf:str13} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:amount_OC} was {bf:str4}, now {bf:str5} to accommodate using data's values)
{p_end}
{p 0 4 2}
file {bf}
C:\Users\myogo\AppData\Local\Temp\ST_9840_000001.tmp{rm}
saved
as .dta format
{p_end}
{res}{text}(7 vars, 113 obs)
{res}{txt}(55 observations deleted)
{p 0 7 2}
(variable
{bf:Name} was {bf:str27}, now {bf:str157} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:Total} was {bf:str6}, now {bf:str13} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:amountUSA} was {bf:str6}, now {bf:str13} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:amountEUR} was {bf:str5}, now {bf:str13} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:amountJPN} was {bf:str3}, now {bf:str13} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:amountGBR} was {bf:str4}, now {bf:str13} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:amountOWN} was {bf:str5}, now {bf:str13} to accommodate using data's values)
{p_end}
{p 0 4 2}
file {bf}
C:\Users\myogo\AppData\Local\Temp\ST_9840_000001.tmp{rm}
saved
as .dta format
{p_end}

{com}. /* 2007-2012 */
> 
> forval y = 2007/2012 {c -(};
{txt}  2{com}.         if `y'==2007 {c -(};
{txt}  3{com}.                 local sheet = "Table 25";
{txt}  4{com}.                 local cell = "A4";
{txt}  5{com}.         {c )-};
{txt}  6{com}.         else if `y'==2008 {c -(};
{txt}  7{com}.                 local sheet = "TB25";
{txt}  8{com}.                 local cell = "A5";
{txt}  9{com}.         {c )-};
{txt} 10{com}.         else {c -(};
{txt} 11{com}.                 local sheet = "A7";
{txt} 12{com}.                 local cell = "A5";
{txt} 13{com}.         {c )-};
{txt} 14{com}.         /* Load data */
> 
>         import excel using "`directory'/shca`y'/shc`y'r-appx",
>                 sheet("`sheet'") cellra(`cell') first allstring clear;
{txt} 15{com}.         /* Rename variables */
> 
>         rename Countryorregion Name;
{txt} 16{com}.         rename US amountUSA;
{txt} 17{com}.         rename Euro amountEUR;
{txt} 18{com}.         rename JapaneseYen amountJPN;
{txt} 19{com}.         rename UKPound amountGBR;
{txt} 20{com}.         rename OwnCurrency amountOWN;
{txt} 21{com}.         keep Name-amountOWN;
{txt} 22{com}.         /* Drop total and footnotes */
> 
>         gen byte total = sum(Name=="Total");
{txt} 23{com}.         drop if total | missing(Total);
{txt} 24{com}.         drop total;
{txt} 25{com}.         /* Construct variables */
> 
>         gen int year = `y';
{txt} 26{com}.         gen byte type = 1;
{txt} 27{com}.         /* Save data */
> 
>         append using `Debt';
{txt} 28{com}.         save `Debt', replace;
{txt} 29{com}. {c )-};
{res}{text}(9 vars, 88 obs)
{res}{txt}(17 observations deleted)
{p 0 7 2}
(variable
{bf:Name} was {bf:str31}, now {bf:str157} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:Total} was {bf:str6}, now {bf:str13} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:amountUSA} was {bf:str6}, now {bf:str13} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:amountEUR} was {bf:str5}, now {bf:str13} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:amountJPN} was {bf:str4}, now {bf:str13} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:amountGBR} was {bf:str4}, now {bf:str13} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:amountOWN} was {bf:str5}, now {bf:str13} to accommodate using data's values)
{p_end}
{p 0 4 2}
file {bf}
C:\Users\myogo\AppData\Local\Temp\ST_9840_000001.tmp{rm}
saved
as .dta format
{p_end}
{res}{text}(7 vars, 91 obs)
{res}{txt}(26 observations deleted)
{p 0 7 2}
(variable
{bf:Name} was {bf:str125}, now {bf:str157} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:Total} was {bf:str6}, now {bf:str13} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:amountUSA} was {bf:str6}, now {bf:str13} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:amountEUR} was {bf:str4}, now {bf:str13} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:amountJPN} was {bf:str3}, now {bf:str13} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:amountGBR} was {bf:str4}, now {bf:str13} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:amountOWN} was {bf:str5}, now {bf:str13} to accommodate using data's values)
{p_end}
{p 0 4 2}
file {bf}
C:\Users\myogo\AppData\Local\Temp\ST_9840_000001.tmp{rm}
saved
as .dta format
{p_end}
{res}{text}(9 vars, 84 obs)
{res}{txt}(26 observations deleted)
{p 0 7 2}
(variable
{bf:Name} was {bf:str150}, now {bf:str157} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:Total} was {bf:str6}, now {bf:str13} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:amountUSA} was {bf:str6}, now {bf:str13} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:amountEUR} was {bf:str5}, now {bf:str13} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:amountJPN} was {bf:str4}, now {bf:str13} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:amountGBR} was {bf:str4}, now {bf:str13} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:amountOWN} was {bf:str5}, now {bf:str13} to accommodate using data's values)
{p_end}
{p 0 4 2}
file {bf}
C:\Users\myogo\AppData\Local\Temp\ST_9840_000001.tmp{rm}
saved
as .dta format
{p_end}
{res}{text}(9 vars, 78 obs)
{res}{txt}(26 observations deleted)
{p 0 7 2}
(variable
{bf:Name} was {bf:str150}, now {bf:str157} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:Total} was {bf:str6}, now {bf:str13} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:amountUSA} was {bf:str6}, now {bf:str13} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:amountEUR} was {bf:str4}, now {bf:str13} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:amountJPN} was {bf:str5}, now {bf:str13} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:amountGBR} was {bf:str4}, now {bf:str13} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:amountOWN} was {bf:str5}, now {bf:str13} to accommodate using data's values)
{p_end}
{p 0 4 2}
file {bf}
C:\Users\myogo\AppData\Local\Temp\ST_9840_000001.tmp{rm}
saved
as .dta format
{p_end}
{res}{text}(9 vars, 91 obs)
{res}{txt}(26 observations deleted)
{p 0 7 2}
(variable
{bf:Total} was {bf:str6}, now {bf:str13} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:amountUSA} was {bf:str6}, now {bf:str13} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:amountEUR} was {bf:str4}, now {bf:str13} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:amountJPN} was {bf:str5}, now {bf:str13} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:amountGBR} was {bf:str4}, now {bf:str13} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:amountOWN} was {bf:str5}, now {bf:str13} to accommodate using data's values)
{p_end}
{p 0 4 2}
file {bf}
C:\Users\myogo\AppData\Local\Temp\ST_9840_000001.tmp{rm}
saved
as .dta format
{p_end}
{res}{text}(19 vars, 83 obs)
{res}{txt}(17 observations deleted)
{p 0 7 2}
(variable
{bf:Total} was {bf:str6}, now {bf:str13} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:amountUSA} was {bf:str6}, now {bf:str13} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:amountEUR} was {bf:str4}, now {bf:str13} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:amountJPN} was {bf:str4}, now {bf:str13} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:amountGBR} was {bf:str4}, now {bf:str13} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:amountOWN} was {bf:str5}, now {bf:str13} to accommodate using data's values)
{p_end}
{p 0 4 2}
file {bf}
C:\Users\myogo\AppData\Local\Temp\ST_9840_000001.tmp{rm}
saved
as .dta format
{p_end}

{com}. /* 2013-2020 */
> 
> forval y = 2013/$year_max {c -(};
{txt}  2{com}.         if `y'==2013 {c -(};
{txt}  3{com}.                 local file = "appendix_tab07";
{txt}  4{com}.         {c )-};
{txt}  5{com}.         else if `y'==2014 {c -(};
{txt}  6{com}.                 local file = "shc14_app07";
{txt}  7{com}.         {c )-};
{txt}  8{com}.         else if `y'==2015 {c -(};
{txt}  9{com}.                 local file = "shc15_app07";
{txt} 10{com}.         {c )-};
{txt} 11{com}.         else {c -(};
{txt} 12{com}.                 local file = "shc_app07_`y'";
{txt} 13{com}.         {c )-};
{txt} 14{com}.         /* Load data */ 
> 
>         import delimited using "`directory'/shca`y'/`file'",
>                 varn(5) case(preserve) rowr(6) stringc(_all) clear;
{txt} 15{com}.         /* Rename variables */
> 
>         rename Countryorregionofissuer Name;
{txt} 16{com}.         cap rename US amountUSA;
{txt} 17{com}.         cap rename USdollar amountUSA;
{txt} 18{com}.         rename Euro amountEUR;
{txt} 19{com}.         cap rename Yen amountJPN;
{txt} 20{com}.         cap rename yen amountJPN;
{txt} 21{com}.         cap rename Pound amountGBR;
{txt} 22{com}.         cap rename pound amountGBR;
{txt} 23{com}.         cap rename Currency amountOWN;
{txt} 24{com}.         cap rename currency amountOWN;
{txt} 25{com}.         keep Name-amountOWN;
{txt} 26{com}.         /* Drop total and footnotes */
> 
>         gen byte total = sum(Name=="Total");
{txt} 27{com}.         drop if total | missing(Total);
{txt} 28{com}.         drop total;
{txt} 29{com}.         /* Construct variables */
> 
>         gen int year = `y';
{txt} 30{com}.         gen byte type = 1;
{txt} 31{com}.         /* Save data */
> 
>         append using `Debt';
{txt} 32{com}.         save `Debt', replace;
{txt} 33{com}. {c )-};
{res}{txt}(encoding automatically selected: ISO-8859-1)
{res}{text}(7 vars, 100 obs)
{res}{txt}(35 observations deleted)
{p 0 7 2}
(variable
{bf:Name} was {bf:str41}, now {bf:str159} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:amountOWN} was {bf:str12}, now {bf:str13} to accommodate using data's values)
{p_end}
{p 0 4 2}
file {bf}
C:\Users\myogo\AppData\Local\Temp\ST_9840_000001.tmp{rm}
saved
as .dta format
{p_end}
{res}{txt}(encoding automatically selected: ISO-8859-1)
{res}{text}(7 vars, 103 obs)
{res}{txt}(39 observations deleted)
{p 0 7 2}
(variable
{bf:Name} was {bf:str40}, now {bf:str159} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:amountEUR} was {bf:str12}, now {bf:str13} to accommodate using data's values)
{p_end}
{p 0 4 2}
file {bf}
C:\Users\myogo\AppData\Local\Temp\ST_9840_000001.tmp{rm}
saved
as .dta format
{p_end}
{res}{txt}(encoding automatically selected: ISO-8859-1)
{text}(7 vars, 93 obs)
{res}{txt}(35 observations deleted)
{p 0 7 2}
(variable
{bf:Total} was {bf:str6}, now {bf:str13} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:amountUSA} was {bf:str6}, now {bf:str14} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:amountEUR} was {bf:str4}, now {bf:str13} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:amountJPN} was {bf:str5}, now {bf:str13} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:amountGBR} was {bf:str4}, now {bf:str13} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:amountOWN} was {bf:str5}, now {bf:str30} to accommodate using data's values)
{p_end}
{p 0 4 2}
file {bf}
C:\Users\myogo\AppData\Local\Temp\ST_9840_000001.tmp{rm}
saved
as .dta format
{p_end}
{res}{txt}(encoding automatically selected: ISO-8859-1)
{text}(7 vars, 96 obs)
{res}{txt}(35 observations deleted)
{p 0 7 2}
(variable
{bf:Total} was {bf:str6}, now {bf:str13} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:amountUSA} was {bf:str6}, now {bf:str14} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:amountEUR} was {bf:str4}, now {bf:str13} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:amountJPN} was {bf:str5}, now {bf:str13} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:amountGBR} was {bf:str4}, now {bf:str13} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:amountOWN} was {bf:str5}, now {bf:str30} to accommodate using data's values)
{p_end}
{p 0 4 2}
file {bf}
C:\Users\myogo\AppData\Local\Temp\ST_9840_000001.tmp{rm}
saved
as .dta format
{p_end}
{res}{txt}(encoding automatically selected: ISO-8859-1)
{text}(7 vars, 96 obs)
{res}{txt}(35 observations deleted)
{p 0 7 2}
(variable
{bf:Total} was {bf:str6}, now {bf:str13} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:amountUSA} was {bf:str6}, now {bf:str14} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:amountEUR} was {bf:str5}, now {bf:str13} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:amountJPN} was {bf:str5}, now {bf:str13} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:amountGBR} was {bf:str4}, now {bf:str13} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:amountOWN} was {bf:str6}, now {bf:str30} to accommodate using data's values)
{p_end}
{p 0 4 2}
file {bf}
C:\Users\myogo\AppData\Local\Temp\ST_9840_000001.tmp{rm}
saved
as .dta format
{p_end}
{res}{txt}(encoding automatically selected: ISO-8859-1)
{text}(7 vars, 104 obs)
{res}{txt}(35 observations deleted)
{p 0 7 2}
(variable
{bf:Total} was {bf:str6}, now {bf:str13} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:amountUSA} was {bf:str6}, now {bf:str14} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:amountEUR} was {bf:str4}, now {bf:str13} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:amountJPN} was {bf:str5}, now {bf:str13} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:amountGBR} was {bf:str4}, now {bf:str13} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:amountOWN} was {bf:str5}, now {bf:str30} to accommodate using data's values)
{p_end}
{p 0 4 2}
file {bf}
C:\Users\myogo\AppData\Local\Temp\ST_9840_000001.tmp{rm}
saved
as .dta format
{p_end}
{res}{txt}(encoding automatically selected: ISO-8859-1)
{text}(7 vars, 102 obs)
{res}{txt}(35 observations deleted)
{p 0 7 2}
(variable
{bf:Total} was {bf:str6}, now {bf:str13} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:amountUSA} was {bf:str6}, now {bf:str14} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:amountEUR} was {bf:str5}, now {bf:str13} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:amountJPN} was {bf:str5}, now {bf:str13} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:amountGBR} was {bf:str4}, now {bf:str13} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:amountOWN} was {bf:str5}, now {bf:str30} to accommodate using data's values)
{p_end}
{p 0 4 2}
file {bf}
C:\Users\myogo\AppData\Local\Temp\ST_9840_000001.tmp{rm}
saved
as .dta format
{p_end}
{res}{txt}(encoding automatically selected: ISO-8859-1)
{res}{text}(7 vars, 79 obs)
{res}{txt}(22 observations deleted)
{p 0 7 2}
(variable
{bf:Name} was {bf:str40}, now {bf:strL} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:amountUSA} was {bf:str13}, now {bf:str14} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:amountOWN} was {bf:str7}, now {bf:str30} to accommodate using data's values)
{p_end}
{p 0 4 2}
file {bf}
C:\Users\myogo\AppData\Local\Temp\ST_9840_000001.tmp{rm}
saved
as .dta format
{p_end}

{com}. /* Replace small holdings */
> 
> foreach var of varlist Total amount* {c -(};
{txt}  2{com}.         replace `var' = "0" if inlist(strtrim(`var'),"*","n.a.");
{txt}  3{com}. {c )-};
{txt}(126 real changes made)
(135 real changes made)
(50 real changes made)
(17 real changes made)
(57 real changes made)
(79 real changes made)
(5 real changes made)

{com}. destring Total amount*, replace;
{txt}Total: all characters numeric; {res}replaced {txt}as {res}long
{txt}amountUSA: all characters numeric; {res}replaced {txt}as {res}long
{txt}amountEUR: all characters numeric; {res}replaced {txt}as {res}long
{txt}amountJPN: all characters numeric; {res}replaced {txt}as {res}long
{txt}(1 missing value generated)
{res}{txt}amountGBR: all characters numeric; {res}replaced {txt}as {res}long
{txt}(1 missing value generated)
{res}{txt}amountOWN: all characters numeric; {res}replaced {txt}as {res}long
{txt}(599 missing values generated)
{res}{txt}amount_OC: all characters numeric; {res}replaced {txt}as {res}long
{txt}(2871 missing values generated)
{res}{txt}
{com}. /* Fix variables */
> 
> egen foreign = rowtotal(amountUSA amountEUR amountJPN amountGBR);
{txt}
{com}. replace amountOWN = max(Total-foreign,0) if year<=2005;
{txt}(598 real changes made)

{com}. drop Total amount_OC foreign;
{txt}
{com}. /* Reshape in long format */
> 
> reshape long amount, i(year Name type) j(currency) string;
{txt}(j = EUR GBR JPN OWN USA)

Data{col 36}Wide{col 43}->{col 48}Long
{hline 77}
Number of observations     {res}       3,468   {txt}->   {res}17,340      
{txt}Number of variables        {res}           8   {txt}->   {res}5           
{txt}j variable (5 values)                     ->   {res}currency
{txt}xij variables:
      {res}amountEUR amountGBR ... amountUSA   {txt}->   {res}amount
{txt}{hline 77}

{com}. /* Save data */
> 
> save `Debt', replace;
{txt}{p 0 4 2}
file {bf}
C:\Users\myogo\AppData\Local\Temp\ST_9840_000001.tmp{rm}
saved
as .dta format
{p_end}

{com}. /* Step 2: Construct equity data */
> 
> /* Load equity data */
> 
> tempfile Equity;
{txt}
{com}. /* 2003-2006 */
> 
> forval y = $year_min/2006 {c -(};
{txt}  2{com}.         if `y'==$year_min {c -(};
{txt}  3{com}.                 local file = "Table 24";
{txt}  4{com}.         {c )-};
{txt}  5{com}.         else {c -(};
{txt}  6{com}.                 local file = "Table 27";
{txt}  7{com}.         {c )-};
{txt}  8{com}.         /* Load data */
> 
>         import excel using "`directory'/shca`y'/`file'",
>                 first allstring clear;
{txt}  9{com}.         /* Rename variables */
> 
>         rename Countryorcategory Name;
{txt} 10{com}.         rename Total amount;
{txt} 11{com}.         rename Common CommonStock;
{txt} 12{com}.         keep Name amount CommonStock;
{txt} 13{com}.         /* Drop total and footnotes */
> 
>         gen byte total = sum(Name=="Total");
{txt} 14{com}.         drop if total | missing(amount);
{txt} 15{com}.         drop total;
{txt} 16{com}.         /* Construct variables */
> 
>         gen int year = `y';
{txt} 17{com}.         /* Save data */
> 
>         if `y'!=$year_min       append using `Equity';
{txt} 18{com}.         save `Equity', replace;
{txt} 19{com}. {c )-};
{res}{text}(6 vars, 131 obs)
{res}{txt}(3 observations deleted)
{p 0 4 2}
(file {bf}
C:\Users\myogo\AppData\Local\Temp\ST_9840_000002.tmp{rm}
not found)
{p_end}
{p 0 4 2}
file {bf}
C:\Users\myogo\AppData\Local\Temp\ST_9840_000002.tmp{rm}
saved
as .dta format
{p_end}
{res}{text}(6 vars, 119 obs)
{res}{txt}(3 observations deleted)
{p 0 7 2}
(variable
{bf:Name} was {bf:str26}, now {bf:str27} to accommodate using data's values)
{p_end}
{p 0 4 2}
file {bf}
C:\Users\myogo\AppData\Local\Temp\ST_9840_000002.tmp{rm}
saved
as .dta format
{p_end}
{res}{text}(6 vars, 114 obs)
{res}{txt}(1 observation deleted)
{p 0 4 2}
file {bf}
C:\Users\myogo\AppData\Local\Temp\ST_9840_000002.tmp{rm}
saved
as .dta format
{p_end}
{res}{text}(7 vars, 117 obs)
{res}{txt}(1 observation deleted)
{p 0 4 2}
file {bf}
C:\Users\myogo\AppData\Local\Temp\ST_9840_000002.tmp{rm}
saved
as .dta format
{p_end}

{com}. /* 2007-2012 */
> 
> forval y = 2007/2012 {c -(};
{txt}  2{com}.         if `y'==2007 {c -(};
{txt}  3{com}.                 local sheet = "Table 30";
{txt}  4{com}.                 local cell = "A4";
{txt}  5{com}.         {c )-};
{txt}  6{com}.         else if `y'==2008 {c -(};
{txt}  7{com}.                 local sheet = "TB30";
{txt}  8{com}.                 local cell = "A5";
{txt}  9{com}.         {c )-};
{txt} 10{com}.         else {c -(};
{txt} 11{com}.                 local sheet = "A12";
{txt} 12{com}.                 local cell = "A5";
{txt} 13{com}.         {c )-};
{txt} 14{com}.         /* Load data */
> 
>         import excel using "`directory'/shca`y'/shc`y'r-appx",
>                 sheet("`sheet'") cellra(`cell') first allstring clear;
{txt} 15{com}.         /* Rename variables */
> 
>         rename Countryorcategory Name;
{txt} 16{com}.         rename Total amount;
{txt} 17{com}.         keep Name amount CommonStock;
{txt} 18{com}.         /* Drop total and footnotes */
> 
>         gen byte total = sum(Name=="Total");
{txt} 19{com}.         drop if total | missing(amount);
{txt} 20{com}.         drop total;
{txt} 21{com}.         /* Construct variables */
> 
>         gen int year = `y';
{txt} 22{com}.         /* Save data */
> 
>         append using `Equity';
{txt} 23{com}.         save `Equity', replace;
{txt} 24{com}. {c )-};
{res}{text}(7 vars, 140 obs)
{res}{txt}(17 observations deleted)
{p 0 4 2}
file {bf}
C:\Users\myogo\AppData\Local\Temp\ST_9840_000002.tmp{rm}
saved
as .dta format
{p_end}
{res}{text}(13 vars, 158 obs)
{res}{txt}(23 observations deleted)
{p 0 4 2}
file {bf}
C:\Users\myogo\AppData\Local\Temp\ST_9840_000002.tmp{rm}
saved
as .dta format
{p_end}
{res}{text}(13 vars, 155 obs)
{res}{txt}(23 observations deleted)
{p 0 4 2}
file {bf}
C:\Users\myogo\AppData\Local\Temp\ST_9840_000002.tmp{rm}
saved
as .dta format
{p_end}
{res}{text}(13 vars, 153 obs)
{res}{txt}(23 observations deleted)
{p 0 4 2}
file {bf}
C:\Users\myogo\AppData\Local\Temp\ST_9840_000002.tmp{rm}
saved
as .dta format
{p_end}
{res}{text}(5 vars, 150 obs)
{res}{txt}(22 observations deleted)
{p 0 4 2}
file {bf}
C:\Users\myogo\AppData\Local\Temp\ST_9840_000002.tmp{rm}
saved
as .dta format
{p_end}
{res}{text}(5 vars, 149 obs)
{res}{txt}(17 observations deleted)
{p 0 4 2}
file {bf}
C:\Users\myogo\AppData\Local\Temp\ST_9840_000002.tmp{rm}
saved
as .dta format
{p_end}

{com}. /* 2013-2020 */
> 
> forval y = 2013/$year_max {c -(};
{txt}  2{com}.         if `y'==2013 {c -(};
{txt}  3{com}.                 local file = "appendix_tab12";
{txt}  4{com}.         {c )-};
{txt}  5{com}.         else if `y'==2014 {c -(};
{txt}  6{com}.                 local file = "shc14_app12";
{txt}  7{com}.         {c )-};
{txt}  8{com}.         else if `y'==2015 {c -(};
{txt}  9{com}.                 local file = "shc15_app12";
{txt} 10{com}.         {c )-};
{txt} 11{com}.         else {c -(};
{txt} 12{com}.                 local file = "shc_app12_`y'";
{txt} 13{com}.         {c )-};
{txt} 14{com}.         /* Load data */ 
> 
>         import delimited using "`directory'/shca`y'/`file'",
>                 varn(5) case(preserve) rowr(6) stringc(_all) clear;
{txt} 15{com}.         /* Rename variables */
> 
>         rename Countryorregionofissuer Name;
{txt} 16{com}.         rename Total amount;
{txt} 17{com}.         cap rename Stock CommonStock;
{txt} 18{com}.         cap rename stock CommonStock;
{txt} 19{com}.         keep Name amount CommonStock;
{txt} 20{com}.         /* Drop total and footnotes */
> 
>         gen byte total = sum(Name=="Total");
{txt} 21{com}.         drop if total | missing(amount);
{txt} 22{com}.         drop total;
{txt} 23{com}.         /* Construct variables */
> 
>         gen int year = `y';
{txt} 24{com}.         /* Save data */
> 
>         append using `Equity';
{txt} 25{com}.         save `Equity', replace;
{txt} 26{com}. {c )-};
{res}{txt}(encoding automatically selected: ISO-8859-1)
{res}{text}(7 vars, 171 obs)
{res}{txt}(35 observations deleted)
{p 0 7 2}
(variable
{bf:Name} was {bf:str41}, now {bf:str160} to accommodate using data's values)
{p_end}
{p 0 4 2}
file {bf}
C:\Users\myogo\AppData\Local\Temp\ST_9840_000002.tmp{rm}
saved
as .dta format
{p_end}
{res}{txt}(encoding automatically selected: ISO-8859-1)
{res}{text}(5 vars, 165 obs)
{res}{txt}(35 observations deleted)
{p 0 7 2}
(variable
{bf:Name} was {bf:str41}, now {bf:str160} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:amount} was {bf:str12}, now {bf:str13} to accommodate using data's values)
{p_end}
{p 0 4 2}
file {bf}
C:\Users\myogo\AppData\Local\Temp\ST_9840_000002.tmp{rm}
saved
as .dta format
{p_end}
{res}{txt}(encoding automatically selected: ISO-8859-1)
{text}(5 vars, 160 obs)
{res}{txt}(31 observations deleted)
{p 0 7 2}
(variable
{bf:amount} was {bf:str7}, now {bf:str13} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:CommonStock} was {bf:str7}, now {bf:str13} to accommodate using data's values)
{p_end}
{p 0 4 2}
file {bf}
C:\Users\myogo\AppData\Local\Temp\ST_9840_000002.tmp{rm}
saved
as .dta format
{p_end}
{res}{txt}(encoding automatically selected: ISO-8859-1)
{text}(5 vars, 155 obs)
{res}{txt}(29 observations deleted)
{p 0 7 2}
(variable
{bf:amount} was {bf:str7}, now {bf:str13} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:CommonStock} was {bf:str7}, now {bf:str13} to accommodate using data's values)
{p_end}
{p 0 4 2}
file {bf}
C:\Users\myogo\AppData\Local\Temp\ST_9840_000002.tmp{rm}
saved
as .dta format
{p_end}
{res}{txt}(encoding automatically selected: ISO-8859-1)
{text}(5 vars, 157 obs)
{res}{txt}(29 observations deleted)
{p 0 7 2}
(variable
{bf:amount} was {bf:str7}, now {bf:str13} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:CommonStock} was {bf:str7}, now {bf:str13} to accommodate using data's values)
{p_end}
{p 0 4 2}
file {bf}
C:\Users\myogo\AppData\Local\Temp\ST_9840_000002.tmp{rm}
saved
as .dta format
{p_end}
{res}{txt}(encoding automatically selected: ISO-8859-1)
{text}(5 vars, 157 obs)
{res}{txt}(29 observations deleted)
{p 0 7 2}
(variable
{bf:amount} was {bf:str7}, now {bf:str13} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:CommonStock} was {bf:str7}, now {bf:str13} to accommodate using data's values)
{p_end}
{p 0 4 2}
file {bf}
C:\Users\myogo\AppData\Local\Temp\ST_9840_000002.tmp{rm}
saved
as .dta format
{p_end}
{res}{txt}(encoding automatically selected: ISO-8859-1)
{text}(5 vars, 156 obs)
{res}{txt}(29 observations deleted)
{p 0 7 2}
(variable
{bf:amount} was {bf:str7}, now {bf:str13} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:CommonStock} was {bf:str7}, now {bf:str13} to accommodate using data's values)
{p_end}
{p 0 4 2}
file {bf}
C:\Users\myogo\AppData\Local\Temp\ST_9840_000002.tmp{rm}
saved
as .dta format
{p_end}
{res}{txt}(encoding automatically selected: ISO-8859-1)
{res}{text}(5 vars, 138 obs)
{res}{txt}(13 observations deleted)
{p 0 7 2}
(variable
{bf:Name} was {bf:str33}, now {bf:strL} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:amount} was {bf:str8}, now {bf:str13} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:CommonStock} was {bf:str7}, now {bf:str13} to accommodate using data's values)
{p_end}
{p 0 4 2}
file {bf}
C:\Users\myogo\AppData\Local\Temp\ST_9840_000002.tmp{rm}
saved
as .dta format
{p_end}

{com}. /* Replace small holdings */
> 
> foreach var of varlist amount CommonStock {c -(};
{txt}  2{com}.         replace `var' = "0" if inlist(strtrim(`var'),"*","n.a.");
{txt}  3{com}. {c )-};
{txt}(227 real changes made)
(193 real changes made)

{com}. destring amount CommonStock, replace;
{txt}amount: all characters numeric; {res}replaced {txt}as {res}long
{txt}CommonStock: all characters numeric; {res}replaced {txt}as {res}long
{txt}
{com}. /* Split into common equity and fund shares */
> 
> expand 2, gen(duplicate);
{txt}(2,282 observations created)

{com}. gen byte type = 3+duplicate;
{txt}
{com}. replace amount = CommonStock if type==3;
{txt}(1,340 real changes made)

{com}. replace amount = max(0,amount-CommonStock) if type==4;
{txt}(1,944 real changes made)

{com}. drop CommonStock duplicate;
{txt}
{com}. /* Step 3: Merge debt and equity data */
> 
> append using `Debt';
{txt}
{com}. /* Fix country name */
> 
> replace Name = regexr(Name,"^Saint","St.");
{txt}(141 real changes made)

{com}. replace Name = regexr(Name," \([1-6]\)$","");
{txt}(293 real changes made)

{com}. replace Name = regexr(Name,", mainland1?$","");
{txt}(192 real changes made)

{com}. replace Name = regexr(Name,", Peoples Republic of$","");
{txt}(24 real changes made)

{com}. replace Name = regexr(Name,", S.A.R.$","");
{txt}(12 real changes made)

{com}. replace Name = regexr(Name,"&","and");
{txt}(13 real changes made)

{com}. replace Name = regexr(Name,"Saint Eustatius","Sint Eustatius");
{txt}(5 real changes made)

{com}. replace Name = "Myanmar" if Name=="Burma";
{txt}(6 real changes made)

{com}. replace Name = "Cabo Verde" if Name=="Cape Verde";
{txt}(14 real changes made)

{com}. replace Name = "Taiwan" if Name=="China, Republic of (Taiwan)";
{txt}(12 real changes made)

{com}. replace Name = "Congo, Republic of" if Name=="Congo (Brazzaville)";
{txt}(71 real changes made)

{com}. replace Name = "Cote d'Ivoire" if regexm(Name,"Cote D('|`)Ivoire");
{txt}(112 real changes made)

{com}. replace Name = "Kiribati" if Name=="Kirabati";
{txt}(2 real changes made)

{com}. replace Name = "South Korea" if Name=="Korea, South";
{txt}(211 real changes made)

{com}. replace Name = "Kyrgyz Republic" if Name=="Kyrgyzstan";
{txt}(9 real changes made)

{com}. replace Name = "Macao" if Name=="Macau";
{txt}(56 real changes made)

{com}. replace Name = "Serbia" if Name=="Serbia and Montenegro";
{txt}(73 real changes made)

{com}. replace Name = "Turks and Caicos Islands" if Name=="Turks and Caicos";
{txt}(4 real changes made)

{com}. recast str64 Name;
{txt}
{com}. /* Convert to US$ billion */
> 
> replace amount = amount/1e3;
{txt}variable {bf}{res}amount{sf}{txt} was {bf}{res}long{sf}{txt} now {bf}{res}double{sf}
{txt}(10,909 real changes made)

{com}. /* Merge country code */
> 
> merge m:1 Name using Countries,
>         keepusing(Counterpart Yeuro)
>         keep(master match);
{res}
{txt}{col 5}Result{col 33}Number of obs
{col 5}{hline 41}
{col 5}Not matched{col 30}{res}             514
{txt}{col 9}from master{col 30}{res}             514{txt}  (_merge==1)
{col 9}from using{col 30}{res}               0{txt}  (_merge==2)

{col 5}Matched{col 30}{res}          21,390{txt}  (_merge==3)
{col 5}{hline 41}

{com}. replace Counterpart = "_OC" if _merge==1;
{txt}(514 real changes made)

{com}. drop Name _merge;
{txt}
{com}. /* Eliminate double counting */
> 
> drop if currency=="OWN" & inlist(Counterpart,"IRQ","MHL","TCA","VGB");
{txt}(66 observations deleted)

{com}. drop if currency=="OWN" & Counterpart=="ECU" & year<=2013;
{txt}(20 observations deleted)

{com}. drop if currency=="OWN" & Counterpart=="ZWE" & year>=2014;
{txt}(7 observations deleted)

{com}. drop if currency=="OWN" & year>=Yeuro;
{txt}(504 observations deleted)

{com}. drop if currency=="OWN" & Counterpart=="JPN";
{txt}(36 observations deleted)

{com}. drop if currency=="OWN" & inlist(Counterpart,"GBR","GGY","IMN","JEY");
{txt}(125 observations deleted)

{com}. drop Yeuro;
{txt}
{com}. /* Assign domestic currency debt */
> 
> replace currency = Counterpart if currency=="OWN";
{txt}(2,710 real changes made)

{com}. /* Aggregate other countries */
> 
> collapse (sum) amount, by(year Counterpart type currency) fast;
{res}{txt}
{com}. /* Construct variables */
> 
> gen country = "USA";
{txt}
{com}. /* Drop zeros */
> 
> drop if amount==0;
{txt}(10,660 observations deleted)

{com}. /* Label variables */
> 
> order year country Counterpart type currency;
{txt}
{com}. label var year          "Year";
{txt}
{com}. label var country       "Investor country";
{txt}
{com}. label var type          "Asset type";
{txt}
{com}. label var currency      "Currency";
{txt}
{com}. label var amount        "Investment amount (US$ billion)";
{txt}
{com}. label define type_label
>         1 "Short-term debt"
>         2 "Long-term debt"
>         3 "Equity"
>         4 "Fund shares";
{txt}
{com}. label val type type_label;
{txt}
{com}. /* Save data */
> 
> sort year country Counterpart type currency;
{txt}
{com}. save Treasury, replace;
{txt}{p 0 4 2}
file {bf}
Treasury.dta{rm}
saved
{p_end}

{com}. log close;
      {txt}name:  {res}<unnamed>
       {txt}log:  {res}C:\Users\myogo\Princeton Dropbox\Motohiro Yogo\Koijen Yogo - International\JPE\Replication\Code\1 Data\Treasury.smcl
  {txt}log type:  {res}smcl
 {txt}closed on:  {res}31 Oct 2025, 09:19:17
{txt}{.-}
{smcl}
{txt}{sf}{ul off}